package com.simple.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.simple.entity.SysUser;

import java.util.List;

public interface SysUserRespository extends JpaRepository<SysUser, Integer> {
    @Query("select a from SysUser  a where a.id=?1 and delStatus=0")
    public SysUser findByUserId(int id);

    @Query("select a from SysUser  a where a.username=?1 and a.password=?2")
    public SysUser userLogin(String username,String password);

    @Query("select a from SysUser  a where a.username=?1")
    public SysUser selectUserName(String username);

    @Query("select a from SysUser  a where a.email=?1")
    public SysUser selectEmail(String email);

    @Query("select a from SysUser  a where a.status=?1")
    public List<SysUser> findAllByStatus(Integer status);

    @Query(value="select * from sys_user  where del_status=0",nativeQuery=true)
    public List<SysUser>  findAll();

    @Query(value="select a from SysUser  a where a.username in ?1")
    public List<SysUser> findByNameList(List<String> list);

    @Modifying
    @Query(value ="UPDATE sys_user SET del_status=1 WHERE id=?1",nativeQuery =true)
    public void deleteUserById(Integer id);
    
    //关联角色
    @Transactional
    @Modifying
    @Query(value="update  sys_user  set role_id=?2  WHERE  id=?1" ,nativeQuery=true)
    public void relationRole(Integer id, Integer roleId);

    //更改用户上线状态
    @Transactional
    @Modifying
    @Query(value="update  sys_user  set online_status=?2  WHERE username=?1" ,nativeQuery=true)
    public void setOnlineStatus(String  username,Integer status);

    //查询所有激活用户，包含未读消息
//    @Query(value="select new com.note.entity.vo.SysUserVo(a.id,a.username,a.headPortrait,a.onlineStatus,(select count(*) from SysChat where receiver=?1 and readStatus=1 and a.id=sender)) "
//            + " from SysUser a left join  SysChat b on b.receiver=a.id  "
//            + " where a.id not in (?1) and a.status=1  GROUP BY a.id order by a.onlineStatus desc")
//    public List<SysUserVo> findAllOnline(Integer id);

    //查在线人数
    @Query(value="SELECT COUNT(*) FROM sys_user WHERE username NOT IN (?1) AND online_status=1",nativeQuery = true)
    public Integer findAllOnlineCount(String username);

    //后台用户激活
    @Transactional
    @Modifying
    @Query(value="UPDATE sys_user SET STATUS=?1 WHERE id=?1",nativeQuery = true)
    public void activationUser(Integer status,Integer userId);

    //头像上传更改
    @Transactional
    @Modifying
    @Query(value="UPDATE sys_user SET head_portrait=?1 WHERE id=?2",nativeQuery = true)
    public void headPortraitUpload(String headPortrait,Integer id);
    
    @Transactional
    @Modifying
    @Query(value="UPDATE sys_user SET password=?1 WHERE id=?2",nativeQuery = true)
    public void modifyPassword(String password,Integer id);
    
}

